package tt.dz.order.util.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;

/**
 * Created by huangchuan on 2017/7/7.
 * Excel导出工具类
 */
public class Exporter<T extends Object> {

    protected HSSFWorkbook workbook = new HSSFWorkbook();

    protected HSSFSheet sheet;

    //当前行
    protected int currentRow = 0;
    //每行的单元格总数
    protected int cellCount = 0;
    //是否有两个表头
    protected boolean twoHeaders = false;

    //Excel模板
    protected ExcelTemplate<T> excelTemplate;

    //Excel样式工具
    protected StyleUtil styleUtil = new StyleUtil();

    protected FormatUtil formatUtil = new FormatUtil();

    //当前行
    protected int currentRow() {
        return currentRow;
    }

    //下一行
    protected void nextRow() {
        currentRow++;
    }

    public int cellCountInJava() {
        return cellCount - 1;
    }

    public Exporter(ExcelTemplate<T> excelTemplate) {
        this.excelTemplate = excelTemplate;

        sheet = workbook.createSheet(excelTemplate.getTitle());

        for (ExcelHeader header : excelTemplate.getHeaders()) {
            cellCount += header.cellSize();
            if (header.cellSize() > 1) {
                twoHeaders = true;
            }
        }
    }

    public void export(HttpServletResponse response) throws Exception {
        createTitleRow();
        createTotalRow();
        createOneHeader();
        if (twoHeaders) {
            createTwoHeader();
        }
        createContextRows();
        widthForCell();

        OutputStream output = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + new String(excelTemplate.getTitle().getBytes("gbk"), "iso8859-1") + ".xls");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setContentType("application/vnd.ms-excel; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;fileName=demo.csv");
        /* 143:149 */       response.setHeader("Content-Transfer-Encoding", "binary");
        /* 144:150 */       response.setContentType("application/vnd.ms-excel; charset=utf-8");
        workbook.write(response.getOutputStream());
        output.close();
    }

    //生成第一行的标题信息
    protected void createTitleRow() {
        HSSFCellStyle headerStyle = styleUtil.createTitleStyle();

        HSSFRow row = sheet.createRow(currentRow());
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(headerStyle);

        addMergedRegionStyle(workbook, sheet, currentRow(), currentRow(), 0, cellCountInJava());
        cell.setCellValue(excelTemplate.getTitle());
        nextRow();
    }

    //生成总计列
    protected void createTotalRow() throws Exception {
        T obj = excelTemplate.getGroup();
        if (obj != null) {
            HSSFCellStyle style = styleUtil.createTotalStyle();
            createContextRow(obj, style);
        }
    }

    //生成概略表头信息
    protected void createOneHeader() {
        HSSFCellStyle style = styleUtil.createHeaderStyle();
        HSSFRow row = sheet.createRow(currentRow());
        int currentColumn = 0;
        for (ExcelHeader header : excelTemplate.getHeaders()) {
            int cells = header.cellSize();
            HSSFCell cell = row.createCell(currentColumn);
            cell.setCellValue(header.getOneHeader());
            cell.setCellStyle(style);

            if (cells == 1) {
                //addMergedRegionStyle(workbook, sheet, currentRow(), currentRow(), currentColumn, currentColumn);
            } else {
                addMergedRegionStyle(workbook, sheet, currentRow(), currentRow(), currentColumn, currentColumn + header.cellSize() - 1);
            }
            currentColumn += header.cellSize();
        }
        nextRow();
    }

    //生成详细表头信息
    protected void createTwoHeader() {
        HSSFCellStyle style = styleUtil.createHeaderStyle();
        HSSFRow row = sheet.createRow(currentRow());
        int currentColumn = 0;
        for (ExcelHeader header : excelTemplate.getHeaders()) {
            int cells = header.cellSize();
            if (cells > 1) {
                for (String str : header.getTwoHeaders()) {
                    HSSFCell cell = row.createCell(currentColumn);
                    cell.setCellValue(str);
                    cell.setCellStyle(style);
                    currentColumn++;
                }
            } else {
                HSSFCell cell = row.createCell(currentColumn);
                cell.setCellStyle(style);
                currentColumn += header.cellSize();
            }
        }
        nextRow();
    }

    //生成列表数据
    protected void createContextRows() throws Exception {
        HSSFCellStyle style = styleUtil.createDefaultStyle();
        for (Object obj : excelTemplate.getList()) {
            createContextRow(obj, style);
        }
    }

    private void createContextRow(Object obj, HSSFCellStyle style) throws Exception {
        HSSFRow row = sheet.createRow(currentRow);
        int currentColumn = 0;

        for (String field : excelTemplate.getFields()) {
            Field clazzField = obj.getClass().getDeclaredField(field);
            clazzField.setAccessible(true);
            HSSFCell cell = row.createCell(currentColumn);
            Object value = clazzField.get(obj);
            setCellStyle(value, cell, style);
            //cell.setCellStyle(style);
            setCellValue(cell, value);
            currentColumn++;
        }
        nextRow();
    }

    private void setCellStyle(Object value, HSSFCell cell, HSSFCellStyle style) {
        if (value == null) {
            cell.setCellStyle(style);
            return;
        }
        if (value instanceof BigDecimal) {
            HSSFDataFormat format = formatUtil.getFormat();
            style.setDataFormat(format.getFormat("0.00"));
            cell.setCellStyle(style);
            return;
        }
        cell.setCellStyle(style);
    }

    private void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
            return;
        }
        cell.setCellValue(value.toString());
    }

    protected void widthForCell() {
        widthForCell(6000);
    }

    protected void widthForCell(int width) {
        for (int i = 0; i < cellCount; i++) {
            sheet.setColumnWidth(i, width);
        }
    }

    //单元格合并
    protected void addMergedRegionStyle(HSSFWorkbook workbook, HSSFSheet sheet, int fromRow, int toRow, int fromCol, int toCol) {
        CellRangeAddress region = new CellRangeAddress(fromRow, (short) toRow, fromCol, (short) toCol);
        sheet.addMergedRegion(region);
        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
    }

    //Excel 样式生成工具
    class StyleUtil {

        HSSFCellStyle createTitleStyle() {
            return createStyle(HSSFColor.CORAL.WHITE.index, HSSFColor.VIOLET.index, (short) 12, HSSFFont.BOLDWEIGHT_BOLD);
        }

        HSSFCellStyle createTotalStyle() {
            return createStyle(HSSFColor.BRIGHT_GREEN.index, HSSFColor.BLACK.index, (short) 11, HSSFFont.BOLDWEIGHT_BOLD);
        }

        HSSFCellStyle createHeaderStyle() {
            return createStyle(HSSFColor.CORAL.WHITE.index, HSSFColor.BLACK.index, (short) 11, HSSFFont.BOLDWEIGHT_BOLD);
        }

        HSSFCellStyle createDefaultStyle() {
            return createStyle(HSSFColor.CORAL.WHITE.index, HSSFColor.BLACK.index, (short) 12, HSSFFont.BOLDWEIGHT_NORMAL);
        }

        HSSFCellStyle createDataStyle() {
            HSSFCellStyle style = createStyle(HSSFColor.CORAL.WHITE.index, HSSFColor.BLACK.index, (short) 12, HSSFFont.BOLDWEIGHT_NORMAL);
            HSSFDataFormat df = workbook.createDataFormat();  //此处设置数据格式
            style.setDataFormat(df.getFormat("##0.00")); //数据格式只显示整数，如果是小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00"));
            return style;
        }

        HSSFCellStyle createStyle(short foregoundColor, short fontColor, short fontPoints, short boldweight) {
            HSSFCellStyle style = workbook.createCellStyle();

            style.setFillForegroundColor(foregoundColor);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            HSSFFont font = workbook.createFont();
            font.setColor(fontColor);
            font.setFontHeightInPoints(fontPoints);
            font.setBoldweight(boldweight);
            style.setFont(font);

            return style;
        }
    }

    class FormatUtil {
        private HSSFDataFormat format = createFormat();

        private HSSFDataFormat createFormat() {
            return workbook.createDataFormat();
        }

        private HSSFDataFormat getFormat() {
            return format;
        }
    }
}
